// 引入mysql模块和配置文件
const mysql = require("mysql");
const config = require("../config/defaults");

// 创建数据库连接池配置，优先使用连接池，直接连接可删除
const pool = mysql.createPool({
  connectionLimit: 10, // 连接池最大连接数
  host: config.database.HOST,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE,
  port: config.database.PORT,
  charset: 'utf8mb4', // 设置字符集为utf8mb4，支持Emoji
});

/**
 * 使用连接池执行查询
 * @param {string} sql - SQL查询语句
 * @param {array} values - 查询参数
 * @returns {Promise} 返回查询结果的Promise
 */
let queryWithPool = (sql, values) => {
  return new Promise((resolve, reject) => {
    // 从连接池获取连接
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err); // 获取连接失败
      } else {
        // 执行SQL查询
        connection.query(sql, values, (err, results) => {
          // 避免变量名冲突，将values改为results
          if (err) {
            reject(err); // 查询出错
          } else {
            resolve(results); // 查询成功
          }
          // 释放连接回连接池
          connection.release();
        });
      }
    });
  });
};

// 添加导出语句
exports.queryWithPool = queryWithPool;

//数据库创建语句
// let BLOGYK =
//   "create database if not exists BLOGYK default charset utf8mb4 collate utf8mb4_unicode_ci";
// 改为MySQL 5.x兼容语法
// let BLOGYK = "create database if not exists BLOGYK default charset utf8 collate utf8_general_ci";
//创建表语句

//用户表
let users = `create table if not exists users(
     id INT NOT NULL AUTO_INCREMENT,
     user_name VARCHAR(100) NOT NULL COMMENT '用户名',
     email VARCHAR(100) NOT NULL COMMENT '邮箱',
     password VARCHAR(100) NOT NULL COMMENT '密码',
     created_at DATETIME NOT NULL COMMENT '创建时间',
     PRIMARY KEY ( id )
    )CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//分组
let subset = `create table if not exists subset(
 id INT NOT NULL AUTO_INCREMENT,
 subset_name VARCHAR(100) NOT NULL COMMENT '分类名称',
 classify INT NOT NULL COMMENT '类型0文章,1图片,2资源',
 created_at DATETIME NOT NULL COMMENT '创建时间',
 PRIMARY KEY ( id )
)CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//本地文件
let file = `create table if not exists file(
     id INT NOT NULL AUTO_INCREMENT,
     url VARCHAR(100) NOT NULL COMMENT '地址',
     file_name VARCHAR(100) NOT NULL COMMENT '名称',
     format VARCHAR(32) NOT NULL COMMENT '格式',
     subset_id INT COMMENT '所属分类',
     created_at DATETIME NOT NULL COMMENT '创建时间',
     PRIMARY KEY ( id )
    )CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//文章
let article = `create table if not exists article(
 id INT NOT NULL AUTO_INCREMENT,
 title VARCHAR(200) NOT NULL COMMENT '标题',
 subset_id INT COMMENT '所属分类',
 classify INT NOT NULL COMMENT '类型0文章,1图片,2文件',
 label VARCHAR(200) COMMENT '标签',
 introduce VARCHAR(1000) COMMENT '简介',
 content TEXT COMMENT '内容',
 cover VARCHAR(100) COMMENT '封面地址',
 views INT DEFAULT 0 COMMENT '查看次数',  
 state INT DEFAULT 0 COMMENT '文章状态',
created_at DATETIME NOT NULL COMMENT '创建时间',
 PRIMARY KEY ( id )
)CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//文章点赞
let praise = `create table if not exists praise(
     id INT NOT NULL AUTO_INCREMENT,
     user_id VARCHAR(100) NOT NULL COMMENT '用户',
     user_type INT NOT NULL COMMENT '查看次数',
     article_id INT  NOT NULL COMMENT '所属文章',
     created_at DATETIME NOT NULL COMMENT '创建时间',
     PRIMARY KEY ( id )
    )CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//文章评论
let comment = `create table if not exists comment(
 id BIGINT NOT NULL AUTO_INCREMENT,
 user_id VARCHAR(100) NOT NULL COMMENT '用户',
 user_type BIGINT NOT NULL COMMENT '用户类型',
 user_name VARCHAR(100) COMMENT '用户名称',
 article_id INT NOT NULL COMMENT '所属文章',
 created_at DATETIME NOT NULL COMMENT '创建时间',
 root_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '一级楼ID',
 parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父ID',
 content VARCHAR(1000) NOT NULL COMMENT '内容',
 complaint INT DEFAULT 0 COMMENT '举报次数',
 isread INT DEFAULT 0 COMMENT '是否已读',
 PRIMARY KEY ( id )
)CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//标签
let label = `create table if not exists label(
     id INT NOT NULL AUTO_INCREMENT,
     label_name VARCHAR(100) NOT NULL COMMENT '名称',
     created_at DATETIME NOT NULL COMMENT '创建时间',
     PRIMARY KEY ( id )
    )CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//日记
let diary = `create table if not exists diary(
 id INT NOT NULL AUTO_INCREMENT,
 title VARCHAR(200) NOT NULL COMMENT '标题',
 content VARCHAR(5000) NOT NULL COMMENT '内容',
 picture VARCHAR(500) COMMENT '图片地址',
 weather_id INT COMMENT '天气',
 mood INT DEFAULT 0 COMMENT '心情',
 created_at DATETIME NOT NULL COMMENT '创建时间',
 PRIMARY KEY ( id )
)CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//天气
let weather = `create table if not exists weather(
     id INT NOT NULL AUTO_INCREMENT,
     weather_name VARCHAR(32) NOT NULL COMMENT '名称',
     icon VARCHAR(100) COMMENT '图标',
     PRIMARY KEY ( id )
    )CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//私信
let message = `create table if not exists message(
 id INT NOT NULL AUTO_INCREMENT,
 user_id VARCHAR(100) NOT NULL COMMENT '用户',
 user_type INT NOT NULL COMMENT '用户类型',
 user_name VARCHAR(100) COMMENT '用户名称',
 created_at DATETIME NOT NULL COMMENT '创建时间',
 content VARCHAR(1000) NOT NULL COMMENT '内容',
 isread INT DEFAULT 0 COMMENT '是否已读',
 PRIMARY KEY ( id )
)CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//埋点
let record = `create table if not exists record(
     id INT NOT NULL AUTO_INCREMENT,
     user_id VARCHAR(100) NOT NULL COMMENT '用户',
     user_type INT NOT NULL COMMENT '用户类型',
     position VARCHAR(100) COMMENT '位置',
     isread INT DEFAULT 0 COMMENT '设备',
     created_at DATETIME NOT NULL COMMENT '创建时间',
     PRIMARY KEY ( id )
    )CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;

//创建数据库语句
// const createDatabase = (db) => {
//   return queryWithPool(db, []); // 使用连接池执行创建数据库操作
// };
//需要手动创建数据库

//创建数据表
const operateSQL = (SQL) => {
  return queryWithPool(SQL, []);
};
(async () => {
  try {
    // 先创建数据库，再创建表
    const tables = [
      users,
      subset,
      file,
      article,
      praise,
      comment,
      label,
      diary,
      weather,
      message,
      record,
    ];

    // 依次异步创建表
    for (const table of tables) {
      await operateSQL(table);
    }
    console.log("全部表创建成功");
  } catch (error) {
    console.error("创建数据库或表时出错:", error);
  }
})();





